package cn.agiledata.bank.myspace.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import cn.agiledata.bank.common.util.Constant;
import cn.agiledata.bank.myspace.bean.PrintTransactionHisBean;
import cn.agiledata.bank.myspace.bean.PrintTransactionHisQueryBean;

public class PrintTransactionHisDaoImp extends JdbcDaoSupport implements
        PrintTransactionHisDao {
    int page[];

    /**
     * 得到打印的页数
     * 
     * @param map
     * @return List保存各个帐户记录数
     */
    public List getTransactionHisBydatePageTotal(Map map) {

        JdbcTemplate jdbcTemplate = new JdbcTemplate(this.getDataSource());
        StringBuffer sql = new StringBuffer();

        sql.append("select s.sId,count(*) from (select s.id as sId, ");
        sql.append("ab.sub_acct_seq,ab.peer_acct_seq,s.main_account_seq,");
        sql.append("ab.trans_id,t.trans_id,a.id ");
        sql.append("from ACCT_BAL_HIS ab,SUB_ACCOUNT s,  transaction t,account a ");
        sql.append("where ab.sub_acct_seq=s.ID  and s.main_account_seq=a.id and ");

        sql.append("ab.TRANS_ID=t.TRANS_ID and ab.AMOUNT <>0 ");

       sqlParam(map, sql);

        sql.append("') s group by s.sId");

        List list = jdbcTemplate.query(sql.toString(), PageCountMapper());

        return list;
    }

    private void sqlParam(Map map, StringBuffer sql) {
        sql.append("and s.MAIN_ACCOUNT_SEQ =");
        sql.append(map.get("accountSeq"));
        sql.append(" and to_char(ab.update_time,'yyyy-MM-dd')>='");
        sql.append(map.get("beginDate"));
        sql.append("' and to_char(ab.update_time,'yyyy-MM-dd')<'");
        sql.append(map.get("endDate"));
    }

    /**
     * 得到每页要打印的数据
     * 
     * @param map
     * @return
     */
    public List getTransactionHisByDatePageList(Map map) {

        JdbcTemplate jdbcTemplate = new JdbcTemplate(this.getDataSource());
        StringBuffer sql = new StringBuffer();                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
        sql.append(" select * from (select row_number() over(ORDER BY h.update_time, h.trans_id) as rownumber_, ");
        sql.append(" h.balance,h.peer_acct_seq as peer_acct_id,h.update_time as begin_date,t.debit_fee,t.credit_fee,t.credit_seq,t.debit_seq,t.purpose,t.remark,t.action_seq,h.amount,t.TRADE_TYPE,t.trans_id,t.account_bank_seq,t.card_no,t.person_name, h.flag");
         

        sql.append(" from ");

        sql.append(" acct_bal_his h,transaction t ");

        sql.append(" where ");
        
        sql.append(" h.trans_id=t.trans_id and h.amount <>0");

        sql.append(" and ");
        		 
        sql.append(" h.sub_acct_seq=");
        
        sql.append("?");
        
        

        sql.append(" and ");

        sql.append(" to_char(h.update_time,'yyyy-MM-dd')<");
        
        sql.append("?");
        
        

        sql.append(" and ");

        sql.append(" to_char(h.update_time,'yyyy-MM-dd')>=");
        
        sql.append("?");
        
        
        
        sql.append(" order by h.update_time, h.trans_id");
        
        sql.append(") WHERE rownumber_ >=");
        
        sql.append("?");
        
        sql.append(" AND rownumber_ <=");
        
        sql.append("?");
        
        final String subAccountID = (String)map.get("subAccountID");
        final String endDate = (String)map.get("endDate");
        final String beginDate = (String)map.get("beginDate");
        final Integer startIndex = (Integer)map.get("startIndex");
        final Integer endIndex = (Integer)map.get("endIndex");
        
        
        List list = jdbcTemplate.query(sql.toString(), 
        		new PreparedStatementSetter() {
            		public void setValues(PreparedStatement ps) throws SQLException {
            			ps.setString(1, subAccountID);
            			ps.setString(2, endDate);
            			ps.setString(3, beginDate);
            			ps.setInt(4, startIndex.intValue());
            			ps.setInt(5, endIndex.intValue());
            		}
        		}
        		,rowMapper());
        

        return list;

    }

    /**
     * 得到上月余额
     * 
     * @param map
     * @return
     */
    public PrintTransactionHisBean getLastMonthBalance(Map map) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(this.getDataSource());
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT b.balance FROM acct_bal_his b WHERE b.ID=(");
        sql.append("SELECT MAX(ID) ");
        sql.append("FROM acct_bal_his a WHERE a.sub_acct_seq=");
        sql.append(map.get("subAccountID"));
        
        sql.append(" and to_char(a.update_time,'yyyy-MM-dd')<'");
        sql.append(map.get("beginDate"));
        sql.append("')");
        List list = jdbcTemplate.query(sql.toString(), balanceMapper());
        PrintTransactionHisBean printBean = null;
        if (list.size() > 0) {
            printBean = (PrintTransactionHisBean) list.get(list.size() - 1);
        } else {
            printBean = new PrintTransactionHisBean();
        }
        return printBean;
    }
    

    private RowMapper PageCountMapper() {
        RowMapper mapper = new RowMapper() {
            public Object mapRow(ResultSet rs, int rowNum) throws SQLException {

                PrintTransactionHisQueryBean printTransHis = new PrintTransactionHisQueryBean();
                printTransHis.setSubAccountId(rs.getLong(1));
                int count = rs.getInt(2);

                printTransHis.setCount(count);

                if (count / Constant.PRINTPAGESIZE == 0) {
                    printTransHis.setPageCount(1);

                } else {
                    if (count % Constant.PRINTPAGESIZE == 0) {
                        printTransHis.setPageCount(count
                                / Constant.PRINTPAGESIZE);
                    } else {
                        printTransHis.setPageCount(count
                                / Constant.PRINTPAGESIZE + 1);
                    }
                }

                return printTransHis;
            }

        };

        return mapper;
    }

    private RowMapper rowMapper() {
        RowMapper mapper = new RowMapper() {
            public Object mapRow(ResultSet rs, int rowNum) throws SQLException {

                PrintTransactionHisBean printTransHis = new PrintTransactionHisBean();
                printTransHis.setBalance(rs.getDouble("balance"));
                printTransHis.setBeginDate(rs.getDate("begin_date"));
                printTransHis.setPeerAcctSeq(rs.getLong("peer_acct_id"));
                printTransHis.setDebitFee(rs.getDouble("debit_fee"));
                printTransHis.setCreditFee(rs.getDouble("credit_fee"));
                
                printTransHis.setCreditSeq(rs.getLong("credit_seq"));
                printTransHis.setDebitSeq(rs.getLong("debit_seq"));
              
                printTransHis.setPurpose(rs.getString("purpose"));
                printTransHis.setRemark(rs.getString("remark"));
                printTransHis.setActionSeq(rs.getLong("action_seq"));
                printTransHis.setAmount(rs.getDouble("amount"));
                printTransHis.setTradeType(rs.getString("TRADE_TYPE"));
              
                printTransHis.setTransId(rs.getString("trans_id"));
                printTransHis.setAccountBankSeq(rs.getLong("account_bank_seq"));
                printTransHis.setCardNO(rs.getString("card_no"));
                printTransHis.setPersonName(rs.getString("person_name"));
                printTransHis.setFlag(rs.getString("flag"));

                return printTransHis;
            }

        };

        return mapper;
    }

    private RowMapper balanceMapper() {
        RowMapper mapper = new RowMapper() {
            public Object mapRow(ResultSet rs, int rowNum) throws SQLException {

                PrintTransactionHisBean printTransHis = new PrintTransactionHisBean();
                printTransHis.setBalance(rs.getDouble("balance"));
               /* printTransHis.setEmail(rs.getString("email"));
                printTransHis.setName(rs.getString("name"));
                printTransHis.setSubAccountName(rs.getString("account_name"));*/

                return printTransHis;
            }

        };

        return mapper;
    }

}
